package com.dai.chun.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dai.chun.dao.dao.DepartmentDao;
import com.dai.chun.dao.model.Department;
import com.dai.chun.dbc.DBManager;
import com.dai.chun.util.PageSplitUtil;

public class DepartmentDaoImpl implements DepartmentDao {
private DBManager manager = DBManager.getInstance();
	

	public List<Department> listDepartment(PageSplitUtil psu) {
		String sql = "select * from t_department limit ?,?";
		List<Department> departments = new ArrayList<Department>();
		Connection conn = null;
		ResultSet res = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, psu.getStart());
			preparedStatement.setInt(2,psu.getEnd());
			res = preparedStatement.executeQuery();
			
			while (res.next()) {
				Department department = new Department();
				department.setDept_id(res.getInt(1));
				department.setDept_name(res.getString(2));
				departments.add(department);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return departments;
	}

	public Boolean save(Department department) throws SQLException {
		boolean flag = false;
		String sql = "insert into t_department values(null,?)";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString( 1,department.getDept_name());
			int count = preparedStatement.executeUpdate(); // 
				if (count > 0) {
					flag = true;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		return flag;
	}

	@Override
	public int findAllCount() throws SQLException {
		int ret=0;
		String sql = "select count(*) from t_department";
		Connection conn = manager.getConnection();
		java.sql.Statement sm=conn.createStatement();
		ResultSet rs=sm.executeQuery(sql);
		if(rs.next()){
			ret=rs.getInt(1);
		}
		manager.closeConn(conn, sm, rs);
		return ret;
	}

	@Override
	public int delDepartment(int dept_id) throws SQLException {
		int ret=0;
		String sql = "delete from t_department where dept_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		preparedStatement.setInt(1 ,dept_id);
		ret=preparedStatement.executeUpdate();
		manager.closeConn(conn, preparedStatement);
		return ret;
		
	}

	@Override
	public int updateDepartment(Department department) throws SQLException {
		int ret=0;
		String sql = "update t_department set dept_name=? where dept_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString( 1,department.getDept_name());
			preparedStatement.setInt(2 ,department.getDept_id());
			int count = preparedStatement.executeUpdate(); // 
				if (count > 0) {
					ret=1;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		
		return ret;
	}

	@Override
	public Department findDepartmentById(int dept_id) {
		String sql = "select * from t_department where dept_id=?";
		Department department =new Department();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
			preparedStatement =  manager.createPRPstmt(conn, sql);
			preparedStatement.setInt(1 ,dept_id);
			res = preparedStatement.executeQuery();
			
			while (res.next()) {
				department.setDept_id(res.getInt(1));
				department.setDept_name(res.getString(2));
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return department;
	}

	public List<Department> listDepartment() {
		String sql = "select * from t_department ";
		List<Department> departments = new ArrayList<Department>();
		Connection conn = null;
		ResultSet res =null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			res = preparedStatement.executeQuery();
			while (res.next()) {
				Department department = new Department();
				department.setDept_id(res.getInt(1));
				department.setDept_name(res.getString(2));
				departments.add(department);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return departments;
	}

}
